// db curd

const mysql = require('mysql');

const async = require("async");
const config = require('../settings');
const pool =  mysql.createPool(config.mysql);

dbquery = function(sql,param,callback){
    pool.getConnection(function(err,conn){
        if(err){
            callback(err,null);
        }else{
            conn.query(sql,param,function(err,data){
                //释放连接
                conn.release();
                //事件驱动回调
                callback(err, data);
            });
        }
    });
};

var _Class = function(tn){
    this.tableName = tn;
}

/**
 * 返回一个公共的sql Promise ,
 * @param sql
 * @param paramArr 类型一定要是个数组对象
 * @returns {Promise}
 */
const makePromise = (sql, paramArr) =>{
    return new Promise((resolve,reject)=>{
            if(paramArr &&　paramArr.constructor == Array){
        dbquery(sql,paramArr, function(err, result){
            if(err){
                return reject(err);
            }
            resolve(result)
        })
    }else{
        return reject(new Error("参数一定要放在数组里"));
    }

})
}

const makeCallBack = (sql, paramArr, cb) =>{
    dbquery(sql, paramArr, function(err, result){
        if(err){
            return cb(err, null);
        }
        cb(null, result)
    })
}

/**
 * 将object拼接到sql中
 * @param obj
 * @param sql
 * @returns {*}
 */
const makeSql = (obj,sql) =>{
    if(obj.hasOwnProperty("orderBy")){
        var orderBy = obj['orderBy']
        delete obj['orderBy'];
        sql += " order By "+ orderBy +" ";
    }
    if(obj.hasOwnProperty("limit")){
        var limit = obj['limit']
        delete obj['limit'];
        //limit['pageNo'] = limit['pageNo'] == 0 ? 1 :limit['pageNo'];
        sql += " limit "+ limit['pageNo']*limit['pageSize']+","+limit['pageSize'];
    }
    return sql;

};
_Class.prototype.findById = function(id,cb){
    var sql = "SELECT * FROM "+this.tableName+" WHERE id = ?"
    makeCallBack(sql, [id], cb);
}
_Class.prototype.findByIdPromise = function(id){
    var sql = "SELECT * FROM "+this.tableName+" WHERE id = ?";
    return makePromise(sql, [id]);
}
_Class.prototype.updateById = function(data,id,cb){
    var sql = "UPDATE "+this.tableName+" set ? WHERE id = ? ";
    makeCallBack(sql, [data,id], cb);
}
_Class.prototype.updateByIdPromise = function(data,id){
    var sql = "UPDATE "+this.tableName+" set ? WHERE id = ? ";
    return makePromise(sql, [data,id]);
}
_Class.prototype.updateByColumns = function(data,obj,cb){
    var apppendQuery = "";
    var columnArr = [data];
    //console.log(columnArr);
    for(var p in obj){
        if(obj.hasOwnProperty(p)){
            apppendQuery += " and ?";
            var temp = {};
            temp[p] = obj[p];
            columnArr.push(temp)
        }
    }
    var sql = "UPDATE "+this.tableName+" set ? WHERE 1 = 1"+apppendQuery;
    //console.log("=======================================");
    //console.log(columnArr);
    //console.log("---------------------------------")
    //console.log(apppendQuery);
    //console.log(sql);
    makeCallBack(sql, columnArr, cb);
}
_Class.prototype.updateByColumnsPromise = function(data,obj){
    var apppendQuery = "";
    var columnArr = [data];
    for(var p in obj){
        if(obj.hasOwnProperty(p)){
            apppendQuery += " and ?";
            var temp = {};
            temp[p] = obj[p];
            columnArr.push(temp)
        }
    }
    var sql = "UPDATE "+this.tableName+" set ? WHERE 1 = 1"+apppendQuery;
    return makePromise(sql, columnArr);
}
_Class.prototype.insert = function(data,cb){
    var sql = "INSERT INTO "+this.tableName+" set ? ";
    makeCallBack(sql, [data], cb);
}
_Class.prototype.insertPromise = function(data){
    var sql = "INSERT INTO "+this.tableName+" set ? ";
    return makePromise(sql, [data]);
}
_Class.prototype.deleteById = function(id,cb){
    var sql = "DELETE FROM "+this.tableName+" WHERE ID = ? ";
    makeCallBack(sql, [id], cb);
}
_Class.prototype.deleteByColumn = function(columnObj,cb){
    var sql = "DELETE FROM "+this.tableName+" WHERE ? ";
    makeCallBack(sql, [columnObj], cb);
}
_Class.prototype.findByColumn = function(columnObj,cb){
    var sql = "select * FROM "+this.tableName+" WHERE ? ";
    makeCallBack(makeSql(columnObj, sql), [columnObj], cb);
}
_Class.prototype.find = function(obj,cb){
    var sql = "select * FROM "+this.tableName;
    makeCallBack(makeSql(obj,sql), [], cb);
}
_Class.prototype.findByColumns = function(obj,cb){

    var apppendQuery = "";
    var columnArr = [];
    for(var p in obj){
        if(p == "limit")
            break;
        if(p == "orderBy")
            break;
        if(obj.hasOwnProperty(p)){
            apppendQuery += " and ?";
            var temp = {};
            temp[p] = obj[p];
            columnArr.push(temp)
        }
    }
    var sql = "select * FROM "+this.tableName+" WHERE 1 = 1"+apppendQuery;
    makeCallBack(makeSql(obj, sql),columnArr, cb);
}
_Class.prototype.findByColumnsPromise = function(obj){

    var apppendQuery = "";
    var columnArr = [];
    for(var p in obj){
        if(p == "limit")
            break;
        if(p == "orderBy")
            break;
        if(obj.hasOwnProperty(p)){
            apppendQuery += " and ?";
            var temp = {};
            temp[p] = obj[p];
            columnArr.push(temp)
        }
    }
    var sql = "select * FROM "+this.tableName+" WHERE 1 = 1"+apppendQuery;
    return makePromise(makeSql(obj, sql), columnArr);
}
_Class.prototype.findBySql = function(sql,cb){
    makeCallBack(sql, [], cb);
}

/**
 * 执行一条sql数据，根据参数
 * @param sql
 * @param param 必须要是个数组
 * @param cb
 */
_Class.prototype.findByParamSql = function(sql,param,cb){
    makeCallBack(sql, param, cb);
}
//不带条件返回记录总数
_Class.prototype.findCount = function(cb){
    var sql = "select count(*) as _c FROM "+this.tableName;
    dbquery(sql,[], function(err, result){
        if(err){
            cb(err, null);
            return;
        }
        cb(null, result[0]["_c"])
    })
}

//带条件返回记录总数
_Class.prototype.findCountByParam = function(obj,cb){
    var sql = "select count(*) as _c FROM "+this.tableName+" WHERE ? ";
    dbquery(sql,obj, function(err, result){
        if(err){
            cb(err, null);
            return;
        }
        cb(null, result[0]["_c"])
    })
}

/**
 * 执行sql,回调函数返回结果集
 * @param sql
 * @param cb
 */
_Class.prototype.sql = function(sql,cb){
    makeCallBack(sql, [], cb);
}
/**
 * @brief 返回一个执行sql的 Promise
 * @parma sql sql语句
 * @return Promise 实例
 */
_Class.prototype.sqlPromise =  (sql) =>{
    return makePromise(sql, []);
}
/**
 * @brief 返回一个执行sql的 Promise
 * @parma sql sql语句
 * @param param 参数的数组 类型位数组
 * @return Promise 实例
 */
_Class.prototype.sqlByParamPromise = (sql,param) =>{
    return makePromise(sql, param);
}
module.exports = _Class;